package sc.tool.sqlgen;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import sc.prototype.common.ConsoleUtil;
import sc.prototype.common.Utils;

public class PoiPlayTool implements ConsoleUtil {

    public static void main(String[] args) {
        try {
            //            String xlsFilePath = "D:\\Temp\\省份、城市、地区、银行信息.xlsx";
            String xlsFilePath = "D:/WeibopayMemo/出款服务FOS/export_new.csv";
            InputStream inp = new FileInputStream(xlsFilePath);
            Workbook wb = new XSSFWorkbook(inp);
            showSheet(wb.getSheetAt(0), 0, 2);
        } catch (Exception e) {
        }
    }

    public static void showSheets(Workbook workbk) {
        Console.info("Sheet Number: {}", workbk.getNumberOfSheets());
        for (int i = 0; i != workbk.getNumberOfSheets(); i++) {
            Sheet sheet = workbk.getSheetAt(i);
            Console.info("Sheet {}, Name {}", i, sheet.getSheetName());
            Console.info(
                    "  Autobreaks:{}, ColumnBreaks:{}, DisplayGuts:{}, FirstRowNum:{}, FitToPage:{}",
                    new Object[] { sheet.getAutobreaks(), Utils.toString(sheet.getColumnBreaks()),
                            sheet.getDisplayGuts(), sheet.getFirstRowNum(), sheet.getFitToPage() });
            //PhysicalNumberOfRows {}, sheet.getPhysicalNumberOfRows(),
        }
    }

    public static void showSheet(Sheet sheet, int beginRow, int showCount) {
        for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext();) {
            if (beginRow > 0) {
                beginRow--;
                continue;
            }
            if (showCount <= 0) {
                break;
            } else {
                showCount--;
            }
            Row row = rit.next();
            for (Iterator<Cell> cit = row.cellIterator(); cit.hasNext();) {
                Cell cell = cit.next();
                // 开始操作单元格
                // 在每一行的输出都打印如 "5:6 例子字符串"，5:6代表第5行，第6列
                // 注意行和列是基于0索引的
                System.out.print(cell.getRowIndex() + ":" + cell.getColumnIndex() + " ");
                // 打印单元格内的数据
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    System.out.println(cell.getRichStringCellValue().getString());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        System.out.println(cell.getDateCellValue());
                    } else {
                        System.out.println(cell.getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.println(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    System.out.println(cell.getCellFormula());
                    break;
                default:
                    System.out.println();
                }
            }
        }
    }
}
